/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.webaccess.retailws.DAO;

import com.webaccess.retailws.Bean.NewsletterBean;
import com.webaccess.retailws.db.DBConnection;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 *
 * @author Owner
 */
public class MenuDAO {

    DBConnection dbc = null;
    Connection conn = null;
    ResultSet rs = null;
    String result = null;
    StringBuffer myQuery = null;

    public MenuDAO() {
        try {
            dbc = new DBConnection();
            //  conn = (Connection) dbc.getConnection();

        } catch (Exception exception) {
            exception.printStackTrace();
        }
    }

    public boolean subscribeNL(NewsletterBean newslBean) {
        boolean isNewsletterSaved = false;
        Long newsletterId = 0L;
        try {
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            myQuery = new StringBuffer();
            myQuery.append("CALL sp_subscribenl(");
            if (newslBean.getNewsLetterId() == 0) {
                myQuery.append("NULL,'");
            } else {
                myQuery.append("'");
                myQuery.append(newslBean.getNewsLetterId());
                myQuery.append("','");
            }
            myQuery.append(newslBean.getNewsLetterEmail());
            myQuery.append("')");
            rs = dbc.executeProcedure(conn, myQuery.toString());
            if (newslBean.getNewsLetterId() == 0) {
                if (rs != null) {
                    String rsValue = String.valueOf(rs);
                    if (!rsValue.equalsIgnoreCase("Result set representing update count of 0")) {
                        while (rs.next()) {
                            newsletterId = rs.getLong("sp_NewsletterId");
                        }
                    }
                }
                if (newsletterId > 0) {
                    isNewsletterSaved = true;
                }
            } else {
                isNewsletterSaved = true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return isNewsletterSaved;
        }
    }

    public Long checkEmail(String newsletterEmail) {

        Long newsLetterId = 0L;
        rs = null;
        try {

            if (conn == null || conn.isClosed()) {
                conn = dbc.getConnection();
            }
            myQuery = new StringBuffer();
            myQuery.append("select newsletter_id,email from newsletter where email = '");
            myQuery.append(newsletterEmail);
            myQuery.append("'");
            rs = dbc.executeQuery(conn, myQuery.toString());
            if (rs != null) {
                String rsValue = String.valueOf(rs);
                if (!rsValue.equalsIgnoreCase("Result set representing update count of 0")) {
                    while (rs.next()) {
                        newsLetterId = rs.getLong("newsletter_id");
                    }
                }
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return newsLetterId;
        }
    }
}
